package cn.gson.CRM.model.dao.salemanage;

import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import cn.gson.CRM.model.entity.Chance;
import cn.gson.CRM.model.vo.MemberTotalVo;

public interface ChanceDao extends CrudRepository<Chance, Long>
{
	@Modifying
	@Query("update Chance ch set ch.quoted=1 where ch.id=?1")
	int updateChanceQuote(Long chanceId);

	@Modifying
	@Query("update Chance ch set ch.quoted=0 where ch.id=?1")
	void setQuotedTo0(Long chance_id);
	
	@Query(value="SELECT MONTH (createDate),count(*) FROM chance_  WHERE "
			+"YEAR (createDate) = ?1  GROUP BY MONTH (createDate) order by MONTH (createDate) asc",
			nativeQuery=true)
	List<Object[]>  getTotalData(String year);
	
	@Query(value="SELECT MONTH (createDate),count(*) FROM chance_  WHERE "
			+"YEAR (createDate) = ?1  and state=?2 GROUP BY MONTH (createDate) order by MONTH (createDate) asc",
			nativeQuery=true)
	List<Object[]>  getTotalData(String year,String state );

	
	@Modifying
	@Query("update Chance ch set ch.plan=1 where ch.id=?1")
	void updateChancePlan(Long id);

	@Query(value="SELECT new cn.gson.CRM.model.vo.MemberTotalVo(g.name,count(od.id)) FROM Chance od "
			+ "LEFT JOIN Member g on od.member3.id = g.id where od.state = 'SUCCESS' GROUP BY g.id "
			+ "ORDER BY count(od.id) DESC")
	List<MemberTotalVo> getMemberPhData(Pageable pageable);
	 
}
